const db = require('../db/index')
exports.addSC = (req, res) => {
  const sc = req.body
  const sqlStr = `select * from sc where id=? and cid=?`
  db.query(sqlStr, [req.auth.id, sc.cid], (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (results.length) {
      return res.cc('请勿重复选课！')
    }
    const sql = `insert into sc set ?`
    db.query(sql, { id: req.auth.id, ...sc }, (err, results) => {
      if (err) {
        return res.cc(err)
      }
      if (results.affectedRows !== 1) {
        return res.cc('添加课程失败，请稍后再试！')
      }
      res.cc('选课成功！', 0)
    })
  })
}
exports.getChoose = (req, res) => {
  const sqlStr = 'select tc.cid,cname,credits,time,name,teachid from course,tc,teacher where tc.id=teacher.id and tc.cid=course.cid'
  db.query(sqlStr, (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (!results.length) {
      return res.cc('获取课程信息失败！')
    }
    res.send({
      status: 0,
      message: '获取课程信息成功！',
      data: results
    })
  })
}
exports.searchCourse = (req, res) => {
  let sqlStr = ''
  let cid = ''
  let cname = ''
  let name = ''
  for (let key in req.query) {
    switch (key) {
      case 'cid':
        cid = req.query['cid']
        break
      case 'cname':
        cname = req.query['cname']
        break
      case 'name':
        name = req.query['name']
        break
      default:
        return res.cc('请求错误！')
    }
  }
  switch (Object.keys(req.query).length) {
    case 3:
      sqlStr = `select tc.cid,cname,credits,time,name,teachid from course,tc,teacher
      where tc.id=teacher.id and tc.cid=course.cid and 
      tc.cid="${cid}" and cname like "${'%' + cname + '%'}" and name="${name}"`
      break
    case 2:
      for (let i = 0; i < 2; i++) {
        if (Object.keys(req.query)[i] === 'cid') {
          req.query['tc.cid'] = req.query[Object.keys(req.query)[i]]
          delete req.query.cid
        }
      }
      if (Object.keys(req.query)[0] === 'cname') {
        sqlStr = `select tc.cid,cname,credits,time,name,teachid from course,tc,teacher 
        where tc.id=teacher.id and tc.cid=course.cid and 
        ${Object.keys(req.query)[0]} like "${'%' + req.query[Object.keys(req.query)[0]] + '%'}" and 
        ${Object.keys(req.query)[1]}="${req.query[Object.keys(req.query)[1]]}"`
      }
      if (Object.keys(req.query)[1] === 'cname') {
        sqlStr = `select tc.cid,cname,credits,time,name,teachid from course,tc,teacher
        where tc.id=teacher.id and tc.cid=course.cid and 
        ${Object.keys(req.query)[0]}="${req.query[Object.keys(req.query)[0]]}" and 
        ${Object.keys(req.query)[1]} like "${'%' + req.query[Object.keys(req.query)[1]] + '%'}"`
      }
      break
    case 1:
      if (Object.keys(req.query)[0] === 'cid') {
        sqlStr = `select tc.cid,cname,credits,time,name,teachid from course,tc,teacher
        where tc.id=teacher.id and tc.cid=course.cid and tc.cid=${req.query[Object.keys(req.query)[0]]}`
        break
      } else if (Object.keys(req.query)[0] === 'cname') {
        sqlStr = `select tc.cid,cname,credits,time,name,teachid from course,tc,teacher
        where tc.id=teacher.id and tc.cid=course.cid and 
        ${Object.keys(req.query)[0]} like "${'%' + req.query[Object.keys(req.query)[0]] + '%'}"`
        break
      }

  }
  db.query(sqlStr, (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (!results.length) {
      return res.cc('未查询到课程信息！')
    }
    res.send({
      status: 0,
      message: '查询课程信息成功！',
      data: results
    })
  })
}
exports.getMy = (req, res) => {
  const sqlStr = `
  select sc.cid,cname,credits,time,teacher.name,sc.teachid
  from course,sc,student,teacher,tc
  where sc.id=student.id and sc.cid=course.cid and tc.id=teacher.id and tc.cid=course.cid 
  and sc.teachid=tc.teachid and student.id=?`
  db.query(sqlStr, req.auth.id, (err, results) => {
    if (err) {
      return res.cc(err)
    }
    res.send({
      status: 0,
      message: '获取我的选课信息成功！',
      data: results
    })
  })
}
exports.deleteSC = (req, res) => {
  let sqlStr = 'delete from sc where id=? and cid=?'
  db.query(sqlStr, [req.auth.id, req.body.cid], (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (results.affectedRows !== 1) {
      return res.cc('退课失败！')
    }
    res.cc('退课成功！', 0)
  })
}
exports.getIs = (req, res) => {
  const sqlStr = `
  select precourse
  from course
  where cid=?`
  db.query(sqlStr, req.query.cid, (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (!results.length) {
      return res.cc('失败！')
    }
    res.send({
      status: 0,
      message: '成功！',
      data: results[0]
    })
  })
}
exports.getPreCourse = (req, res) => {
  const sqlStr = `
  select *
  from course
  where cid=? and precourse is not null and precourse in(select cid from sc where sc.id=?)`
  db.query(sqlStr, [req.query.cid, req.auth.id], (err, results) => {
    if (err) {
      return res.cc(err)
    }
    if (!results.length) {
      return res.cc('失败！')
    }
    res.send({
      status: 0,
      message: '成功！'
    })
  })
}